<?php
/**
 * Created by PhpStorm.
 * User: a
 * Date: 2016/6/7
 * Time: 17:32
 */
namespace Admin\Model;
use Think\Model;

class ReportModel extends Model{
    protected $trueTableName = 'bypal_borrow_info';

    /**
     * 个人申请报文
     * @return mixed
     */
    public function get_borrow_apply_info($start_time,$end_time,$borrow_status){
        $sql="select t1.borrow_id,t1.borrow_money,t1.borrow_loanperiod,FROM_UNIXTIME(t1.borrow_createdate,'%Y%m%d') borrow_createdate,
        t2.cust_name,t2.cust_identity,t3.ls_value,t1.borrow_stateid
        from bypal_borrow_info t1 left join bypal_cust_info t2 on t1.borrow_custid=t2.cust_id
        left join bypal_cust_loanstatus t3 on t1.borrow_stateid=t3.ls_id where 1=1 ";
        if($start_time!=''&&$end_time!=''){
            $start_time=strtotime($start_time);
            $end_time=strtotime($end_time);
            $sql.=" and t1.borrow_createdate between $start_time and $end_time";
        }
        if($borrow_status!=''){
            $sql.=" and t1.borrow_stateid=$borrow_status";
        }
        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }

    /**
     * 个人基本信息报文
     * @param $start_time
     * @param $end_time
     * @return mixed
     */
    public function get_person_info($start_time,$end_time){
        $sql="select t1.cust_name,t1.cust_identity,IFNULL(t1.cust_sex,9) cust_sex,IFNULL(t3.m_code,90) marry_status,IFNULL(t4.edu_code,99) education_status,
        IFNULL(t5.deg_code,9) degree_status,t1.cust_phone,t1.cust_email,t2.communication_province,t2.communication_city,t2.communication_district,
        t2.communication_address,IFNULL(t2.communication_postcode,999999) communication_postcode,t6.first_linkman_name,t6.first_linkman_type,t6.first_linkman_phone,t6.second_linkman_name,
        t6.second_linkman_type,t6.second_linkman_phone,IFNULL(t8.pos_code,'Z') pos_code,IFNULL(t7.company_name,'暂缺') company_name,
        t7.company_province,t7.company_city,t7.company_district,t7.company_address,IFNULL(t9.du_code,9) du_code,IFNULL(t10.ti_code,0) ti_code,
        t2.live_province,t2.live_city,t2.live_district,t2.live_address,IFNULL(t2.live_postcode,999999) live_postcode,IFNULL(t11.li_code,9) li_code
        from bypal_cust_info t1
        left join bypal_cust_detailinformation t2 on t1.cust_id=t2.cust_id
        left join bypal_cust_marital t3 on t2.marry_status_id=t3.m_id
        left join bypal_cust_education t4 on t2.education_status_id=t4.edu_id
        left join bypal_cust_degree t5 on t2.degree_status_id=t5.deg_id
        left join bypal_cust_linkman t6 on t1.cust_id =t6.cust_id
        left join bypal_cust_workdetail t7 on t1.cust_id=t7.cust_id
        left join bypal_cust_position t8 on t7.profession_status_id=t8.pos_id
        left join bypal_cust_duty t9 on t7.duty_status=t9.du_id
        left join bypal_cust_title t10 on t7.title_status=t10.ti_id
        left join bypal_cust_livestatus t11 on t2.live_status=t11.li_id where 1=1 ";

        if($start_time!=''&&$end_time!=''){
            $start_time=strtotime($start_time);
            $end_time=strtotime($end_time);
            $sql.=" and t1.cust_createdate between '$start_time' and '$end_time'";
        }

        $Model = new \Think\Model();
        $values=$Model->query($sql);
        return $values;
    }
}